package com.neuedu.oasystem.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neuedu.oasystem.dao.IProjectDao;
import com.neuedu.oasystem.entity.Project;
import com.neuedu.oasystem.util.DBHelper;
/**
 * 
 * @author QXT
 *
 */
public class ProjectDaoImpl extends BaseDao implements IProjectDao {

	@Override
	public boolean add(Project param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into project(project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name) values(?,?,?,?,?,?,?) ";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, param.getProjectId());
			stmt.setString(2, param.getProjectName());
			stmt.setInt(3, param.getCustomerId());
			stmt.setDouble(4, param.getProjectAmount());
			stmt.setDate(5, new java.sql.Date(param.getProjectFuration()
					.getTime()));
			stmt.setInt(6, param.getProjectLeaderId());
			stmt.setString(7, param.getProjectLeaderName());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public boolean edit(Project param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "update project set project_Name=?,customer_Id=?,project_Amount=?,project_Furation=?,project_Leader_Id=?,project_Leader_Name=? where project_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(7, param.getProjectId());
			stmt.setString(1, param.getProjectName());
			stmt.setInt(2, param.getCustomerId());
			stmt.setDouble(3, param.getProjectAmount());
			stmt.setDate(4, new java.sql.Date(param.getProjectFuration()
					.getTime()));
			stmt.setInt(5, param.getProjectLeaderId());
			stmt.setString(6, param.getProjectLeaderName());
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public boolean deleteById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from project where project_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public Project findById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		Project project = null;
		ResultSet rs = null;

		try {
			String sql = "select project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name from project where project_Id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				int projectId = rs.getInt("project_Id");
				String projectName = rs.getString("project_Name");
				int customerId = rs.getInt("customer_Id");
				double projectAmount = rs.getDouble("project_Amount");
				Date projectFuration = rs.getDate("project_Furation");
				int projectLeaderId = rs.getInt("project_Leader_Id");
				String projectLeaderName = rs
						.getString("project_Leader_Name");
				project = new Project(projectId, projectName,customerId, projectAmount,
						projectFuration, projectLeaderId, projectLeaderName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return project;
	}

	@Override
	public List<Project> findAll() {
		List<Project> list = new ArrayList<Project>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			String sql = "select project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name from project";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int projectId = rs.getInt("project_Id");
				String projectName = rs.getString("project_Name");
				int customerId = rs.getInt("customer_Id");
				double projectAmount = rs.getDouble("project_Amount");
				Date projectFuration = rs.getDate("project_Furation");
				int projectLeaderId = rs.getInt("project_Leader_Id");
				String projectLeaderName = rs
						.getString("project_Leader_Name");
				Project project = new Project(projectId, projectName,customerId,
						projectAmount, projectFuration, projectLeaderId,
						projectLeaderName);
				list.add(project);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}
		return list;
	}

	@Override
	public List<Project> findByExample(Project param) {
		Connection conn = DBHelper.openConnection();
		List<Project> list = new ArrayList<Project>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name from project where project_Name like ? ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, "%" + param.getProjectName() + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				int projectId = rs.getInt("project_Id");
				String projectName = rs.getString("project_Name");
				int customerId = rs.getInt("customer_Id");
				double projectAmount = rs.getDouble("project_Amount");
				Date projectFuration = rs.getDate("project_Furation");
				int projectLeaderId = rs.getInt("project_Leader_Id");
				String projectLeaderName = rs.getString("project_Leader_Name");
				Project project = new Project(projectId, projectName,customerId,
						projectAmount, projectFuration, projectLeaderId,
						projectLeaderName);
				list.add(project);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public List<Project> findByPager(int page, int size) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		List<Project> list = new ArrayList<Project>();
		ResultSet rs = null;
		try {
			String sql = "select * from"
					+ "(select rownum rn,project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name from project where rownum<=?) where rn >? ";

			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, page * size);
			stmt.setInt(2, (page - 1) * size);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int projectId = rs.getInt("project_Id");
				String projectName = rs.getString("project_Name");
				int customerId = rs.getInt("customer_Id");
				double projectAmount = rs.getDouble("project_Amount");
				Date projectFuration = rs.getDate("project_Furation");
				int projectLeaderId = rs.getInt("project_Leader_Id");
				String projectLeaderName = rs
						.getString("project_Leader_Name");
				Project project = new Project(projectId, projectName,customerId,
						projectAmount, projectFuration, projectLeaderId,
						projectLeaderName);
				list.add(project);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public int deleteByIds(int[] ids) {
		Connection conn = DBHelper.openConnection();
		int result = 0;
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "delete from project where project_Id=?";
			stmt = conn.prepareStatement(sql);
			for (int projectId : ids) {
				stmt.setInt(1, projectId);
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i :counts) {
				if (i==-2) {
					result++;
				}
			}
			if (result == ids.length) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn!=null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		
		return result;
	}

	@Override
	public int addAll(List<Project> useparamrs) {
		Connection conn = DBHelper.openConnection();
		int result = 0;
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into project(project_Id,project_Name,customer_Id,project_Amount,project_Furation,project_Leader_Id,project_Leader_Name) values(?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			for (Project i:useparamrs) {
			
				stmt.setInt(1, i.getProjectId());
				stmt.setString(2, i.getProjectName());
				stmt.setInt(3, i.getCustomerId());
				stmt.setDouble(4, i.getProjectAmount());
				stmt.setDate(5, new java.sql.Date(i.getProjectFuration().getTime()));
				stmt.setInt(6, i.getProjectLeaderId());
				stmt.setString(7, i.getProjectLeaderName());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i :counts) {
				if (i==-2) {
					result++;
				}
			}
			if (result == useparamrs.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn!=null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return result;
	}

	@Override
	public int editAll(List<Project> param) {
		Connection conn = DBHelper.openConnection();
		int result = 0;
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "update project set project_Name=?,customer_Id=?,project_Amount=?,project_Furation=?,project_Leader_Id=?,project_Leader_Name=? where project_Id=?";
			stmt = conn.prepareStatement(sql);
			for (Project i:param ){
				stmt.setInt(7, i.getProjectId());
				stmt.setString(1, i.getProjectName());
				stmt.setInt(2, i.getCustomerId());
				stmt.setDouble(3, i.getProjectAmount());
				stmt.setDate(4, new java.sql.Date(i.getProjectFuration().getTime()));
				stmt.setInt(5, i.getProjectLeaderId());
				stmt.setString(6, i.getProjectLeaderName());
				stmt.addBatch();
			}
			int[] counts = stmt.executeBatch();
			for (int i:counts) {
				if (i==-2) {
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if (conn!=null) {
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (Exception e2) {
				}
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}
		return result;
	}

}
